-- exec sp_shipment_quarter_report 1
ALTER                proc sp_shipment_quarter_report

@order_type int

as
begin
	-- CoolUnitCost and SOEndUser fields added into results table by Gandhi on 13th Sep 2004
	declare @results Table( ProductID int, COOlQty int, COOLReqDate datetime, COOLSchDate datetime, 
				PartNo varchar(50), CSLITQtyShipped int, CSLITShippedDate datetime, 
				SoNo varchar(50), CustId int, SoPoNo varchar(30), 
				SoBkDate datetime, SoShipVia varchar(100), CustName varchar(65), CustNo varchar(20), 
				CSTstatus varchar(15), CSTShipDate datetime, CSTTrackingNumber varchar(150),
				Qtr int,Yr int, Mth int, COOLUnitCost float, SoEndUser varchar(65),COOlLineItemNo int,IsDistributor int)

	
	declare @productID int, @cOOlQty int
	declare @cOOLReqDate datetime, @cOOLSchDate datetime
	declare @partNo varchar(50)
	declare @cSLITQtyShipped int
	declare @cSLITShippedDate datetime
	declare @soNo varchar(50)
	declare @cust_id int
	declare @soPoNo varchar(30)
	declare @soBkDate datetime

	declare @cSTShipVia varchar(100)
	declare @cSTShipMethod varchar(50)
	declare @cSTShipAccount varchar(50)

	declare @custName varchar(65),@custNo varchar(20), @cSTstatus varchar(15)
	
	declare @cSTShipDate datetime
	declare @cSTTrackingNumber varchar(150)
	declare @year int
	declare @quarter int
	
	
	declare @temp_year int
	declare @temp_q1_from varchar(20)
	declare @q1_from datetime,@q1_to datetime,@q2_from datetime,@q2_to datetime
	declare @q3_from datetime,@q3_to datetime,@q4_from datetime,@q4_to datetime,@q5_from datetime,@q5_to datetime
	declare @month int
	
	declare @cOOLUnitCost float
	declare @soEndUser varchar(65)
	declare @cOOLLineItemNo int 
	declare @isdistributor	int
	declare mainCursor cursor for
	SELECT
		Customer_Open_Order_LineItem.Product_ID, 
		Customer_Open_Order_LineItem.COOL_Qty, 
		Customer_Open_Order_LineItem.COOL_Requested_Date, 
		Customer_Open_Order_LineItem.COOL_Schedule_Date,
		Product.Part_Number,
		Customer_Shipping_Line_Item_Table.CSLIT_Qty_Shipped, 
		Customer_Shipping_Line_Item_Table.CSLIT_Shipped_Date,
		Sales_Order.SalesOrder_No, 
		Sales_Order.Customer_ID, 
		Sales_Order.SO_Customer_PO_Number, 
		Sales_Order.SO_Booking_Date, 
		--Sales_Order.SO_Shipping_Method, 
		--Sales_Order.SO_Shipping_Account,
		Customer_Shipping_Table.CST_Method,
		Customer_Shipping_Table.CST_Account_Number,
		C1.C_Name, C1.C_Number,C1.C_Is_Distributor,
		Customer_Shipping_Table.CST_Status, 
		Customer_Shipping_Table.CST_Ship_Date, 
		Customer_Shipping_Table.CST_AW_Bill_Number,
		Customer_Open_Order_LineItem.COOL_Unit_Cost, 
		--Sales_Order.SO_EndUser,
		CASE 
		WHEN ( Sales_Order.SO_ODM_ID IS NULL OR Sales_Order.SO_ODM_ID = -1 ) THEN ''
		ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO_ODM_ID )
		END as EndUser,
		Customer_Open_Order_LineItem.COOL_Line_Item_No
		
	FROM
	     ((((Customer_Open_Order_LineItem INNER JOIN  Customer_Shipping_Line_Item_Table ON
	        Customer_Open_Order_LineItem.LineItem_ID = Customer_Shipping_Line_Item_Table.LineItem_ID)
	     INNER JOIN Sales_Order ON Customer_Open_Order_LineItem.SalesOrder_ID = Sales_Order.SalesOrder_ID and Sales_Order.SO_Is_Sample = @order_type)
	     INNER JOIN Product ON Customer_Open_Order_LineItem.Product_ID = Product.Product_ID)
	     INNER JOIN Customer_Shipping_Table ON  Customer_Shipping_Line_Item_Table.CustomerShipping_ID = Customer_Shipping_Table.CustomerShipping_ID)
	     INNER JOIN Customer C1 ON Sales_Order.Customer_ID = C1.Customer_ID	     
	WHERE
	    Customer_Shipping_Table.CST_Status = 'SHIPPED'
	-- To Show Distributor Ship Info. @isdistributor = 2 Customer Ship,=1 Distributoor Ship
	union

	SELECT 
		DLIT.PRODUCT_ID,DLIT.DIST_REQ_QTY,DLIT.DIST_REQ_DATE,null,
		(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = DLIT.PRODUCT_ID) AS PRODUCT,
		DLIT.DIST_SHIP_QTY,DLIT.DIST_SHIP_DATE,DSO.DIST_SALESORDER_NO,DSO.CUSTOMER_ID,
		DSO.CUSTOMER_PO,DSO.SALESORDER_DATE,null,null,
		(SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = DSO.CUSTOMER_ID ),
		(SELECT C_Number FROM CUSTOMER WHERE CUSTOMER_ID = DSO.CUSTOMER_ID ),2,null,
		DLIT.DIST_SHIP_DATE,null,DLIT.UNIT_COST,null,DLIT.DIST_LINEITEM_NO
	FROM 
		 (DIST_SALES_ORDER DSO INNER JOIN DIST_LINE_ITEM_TABLE DLIT ON
	          DSO.DIST_SALESORDER_ID = DLIT.DIST_SALESORDER_ID)

	
		open mainCursor
		fetch next from mainCursor into 	
		@productID, @cOOlQty, @cOOLReqDate, @cOOLSchDate, @partNo, @cSLITQtyShipped, @cSLITShippedDate, 
		@soNo, @cust_id, @soPoNo, @soBkDate, @cSTShipMethod, @cSTShipAccount, @custName, @custNo,@isdistributor, @cSTstatus, @cSTShipDate, @CSTTrackingNumber, @cOOLUnitCost, @soEndUser,@cOOLLineItemNo
		while @@fetch_status = 0
		begin
			--print 'before'
			if @cStShipDate is not null
			begin
				
				/*if(ltrim(rtrim(@soShipMethod)) = '')
				begin	
					set @soShipVia = @soShipAccount
				end
				else
				begin
					set @soShipVia = @soShipMethod + '   #   ' + @soShipAccount
				end*/
				set @cSTShipVia = @cSTShipMethod
				
				set @month = DATEPART(m,@cStShipDate)
				set @year=DATEPART(yyyy,@cStShipDate)
				-- Modified by Prabhanjan (14 Apr 2004)
				--if @month >=2 and @month <=4
				if @month >=1 and @month <=3
				begin
					set @quarter = 1
					--set @month = 4
					set @month = 3
					insert into @results values(@productID, @cOOlQty, @cOOLReqDate, @cOOLSchDate, @partNo, @cSLITQtyShipped, @cSLITShippedDate, 
					@soNo, @cust_id, @soPoNo, @soBkDate, @cSTShipVia, @custName, @custNo, @cSTstatus, @cSTShipDate, @CSTTrackingNumber,@quarter,@year,@month, @cOOLUnitCost, @soEndUser, @cOOLLineItemNo,isnull( @isdistributor, 2 ) )
				end
				--else if  @month >=5 and @month <=7
				else if  @month >=4 and @month <=6
				begin
					set @quarter = 2
					--set @month = 7
					set @month = 6
					insert into @results values(@productID, @cOOlQty, @cOOLReqDate, @cOOLSchDate, @partNo, @cSLITQtyShipped, @cSLITShippedDate, 
					@soNo, @cust_id, @soPoNo, @soBkDate, @cSTShipVia, @custName, @custNo, @cSTstatus, @cSTShipDate, @CSTTrackingNumber,@quarter,@year,@month, @cOOLUnitCost, @soEndUser, @cOOLLineItemNo,isnull( @isdistributor, 2 ))
					set @quarter =null
				end
				--else if @month >=8 and @month <=10
				else if @month >=7 and @month <=9
				begin
					set @quarter = 3
					--set @month = 10
					set @month = 9
					insert into @results values(@productID, @cOOlQty, @cOOLReqDate, @cOOLSchDate, @partNo, @cSLITQtyShipped, @cSLITShippedDate, 
					@soNo, @cust_id, @soPoNo, @soBkDate, @cSTShipVia, @custName, @custNo, @cSTstatus, @cSTShipDate, @CSTTrackingNumber,@quarter,@year,@month, @cOOLUnitCost, @soEndUser, @cOOLLineItemNo,isnull( @isdistributor, 2 ))
					set @quarter =null
				end
				--else if @month =11 or  @month =12 
				else if @month >=10 or  @month <=12 
				begin
					set @quarter = 4
					--set @month = 1
					set @month = 12
					--set @year = convert(int,DATEPART(yyyy,dateadd(m,12,@cStShipDate)))
					insert into @results values(@productID, @cOOlQty, @cOOLReqDate, @cOOLSchDate, @partNo, @cSLITQtyShipped, @cSLITShippedDate, 
					@soNo, @cust_id, @soPoNo, @soBkDate, @cSTShipVia, @custName, @custNo, @cSTstatus, @cSTShipDate, @CSTTrackingNumber,@quarter,@year,@month, @cOOLUnitCost, @soEndUser, @cOOLLineItemNo,isnull( @isdistributor, 2 ))
					set @quarter =null
				end
				/*
				else if @month =1
				begin
					set @quarter = 4
					--set @year = convert(int,DATEPART(yyyy,dateadd(m,-12,@cStShipDate)))
					insert into @results values(@productID, @cOOlQty,@cOOLUnitCost,@cOOLReqDate, @cOOLSchDate,@partNo,@cSLITQtyShipped,@cSLITShippedDate, 
					@soNo,@cust_id, @soPoNo,@soPerson,@soBkDate,@regRep, @custName,@custNo, @cSTstatus,@cSTShipDate,@invNo,@quarter,@year,@month)
					set @quarter =null
				end
				*/
			end
			else
			begin
				--blind insert
					insert into @results values(@productID, @cOOlQty, @cOOLReqDate, @cOOLSchDate, @partNo, @cSLITQtyShipped, @cSLITShippedDate, 
					@soNo, @cust_id, @soPoNo, @soBkDate, @cSTShipVia, @custName, @custNo, @cSTstatus, @cSTShipDate, @CSTTrackingNumber,@quarter,@year,@month, @cOOLUnitCost, @soEndUser, @cOOLLineItemNo,isnull( @isdistributor, 2 ))
			end
		
		set @productID=null
		set @cOOlQty=null
		set @cOOLReqDate=null
		set @cOOLSchDate=null
		set @partNo=null
		set @cSLITQtyShipped=null
		set @cSLITShippedDate=null 
		set @soNo=null
		set @cust_id=null
		set @soPoNo=null
		set @soBkDate=null
		set @cSTShipVia=null
		set @cSTShipMethod=null
		set @cSTShipAccount=null 
		set @custName=null
		set @custNo=null
		set @cSTstatus=null
		set @cSTShipDate=null
		set @cSTTrackingNumber=null
		set @quarter=null
		set @year=null
		set @month=null
		set @cOOLUnitCost=null
		set @soEndUser=null
		set @cOOLLineItemNo=null
		set @isdistributor = null

		fetch next from mainCursor into 	
		@productID, @cOOlQty, @cOOLReqDate, @cOOLSchDate, @partNo, @cSLITQtyShipped, @cSLITShippedDate, 
		@soNo, @cust_id, @soPoNo, @soBkDate, @cSTShipMethod, @cSTShipAccount, @custName, @custNo,@isdistributor, @cSTstatus, @cSTShipDate, @CSTTrackingNumber, @cOOLUnitCost, @soEndUser, @cOOLLineItemNo
		end
		close mainCursor
		deallocate mainCursor
	select * from @results order by Yr, Qtr

end